import datetime
import json

import pandas as pd

import sql_util
from work_zykj.mysql import mysql_util

from concurrent.futures import ThreadPoolExecutor

province_list = []
company_code_list = []
cv_code_list = []
cv_type_list = []
sale_qty_list = []
farm_list = []
farm_type_list = []
capacity_list = []
capacity_crm_list = []
capacity_piglet_crm_list = []
capacity_piglet_list = []


def handle_row_data(row):
    # print(row['push_content'])
    province = row['province']
    company_code = row['company_code']
    cv_code = row['cv_code']
    cv_type = row['cv_type']
    sale_qty = row['sale_qty']
    farm = ''
    farm_type = ''
    capacity = ''
    capacity_crm = ''
    capacity_piglet = ''
    capacity_piglet_crm = ''
    data_list = []
    data = {}

    # print('%s-%s' % (org_code, order_id))
    sql = "select distinct org_code,farm_org,farm_type,breed_scale,fat_scale from ( \
            select t.org_code,t.farm_org,'1' as farm_type,0 fat_scale,0 breed_scale from pc_ss_relation t where t.cv_code = '{}' \
            union all \
            select farm_id as org_code,farm_id as farm_org,'0' as farm_type,i.fat_scale,i.breed_scale from bae_farm_information i where i.cus_code = '{}' \
            ) a ".format(cv_code, cv_code)
    rs = mysql_util.select_by_sql(sql)
    rs = json.loads(rs)
    if len(rs) > 0:
        for f in rs:
            org_code = f['org_code']
            farm_org = f['farm_org']
            farm_type = f['farm_type']
            if '1' == farm_type:
                farm = org_code + "#" + farm_org
                farm_type = '专业版'
                try:
                    farm_org_split = farm_org.split('-')[0]
                    # 查询专业版规模
                    sql = "select t.capacity,t.capacity_piglet from fr_mas_farm_information t where t.org_code = '{}' " \
                          "and farm = '{}'".format(org_code, farm_org_split)
                    rs = sql_util.select_by_org(org_code, sql)
                    rs = json.loads(rs)
                    if len(rs) > 0:
                        capacity = rs[0]['CAPACITY']
                        capacity_piglet = rs[0]['CAPACITY_PIGLET']
                except Exception as e:
                    print(e)

            else:
                farm = org_code
                farm_type = '家庭版'
                # 查询家庭版规模
                capacity = f['breed_scale']
                capacity_piglet = f['fat_scale']
            data['province'] = province
            data['company_code'] = company_code
            data['cv_code'] = cv_code
            data['cv_type'] = cv_type
            data['sale_qty'] = sale_qty
            data['farm'] = farm
            data['farm_type'] = farm_type
            data['capacity'] = capacity
            data['capacity_crm'] = capacity_crm
            data['capacity_crm'] = capacity_crm
            data['capacity_piglet'] = capacity_piglet
            data['capacity_piglet_crm'] = capacity_piglet_crm
            data_list.append(data)
    else:
        data['province'] = province
        data['company_code'] = company_code
        data['cv_code'] = cv_code
        data['cv_type'] = cv_type
        data['sale_qty'] = sale_qty
        data['farm'] = farm
        data['farm_type'] = farm_type
        data['capacity'] = capacity
        data['capacity_crm'] = capacity_crm
        data['capacity_crm'] = capacity_crm
        data['capacity_piglet'] = capacity_piglet
        data['capacity_piglet_crm'] = capacity_piglet_crm
        data_list.append(data)
    return data_list


if __name__ == '__main__':
    file_path = 'C:/Users/yangjianzhang/Desktop/销量大于4吨.xlsx'
    # 使用read_excel函数读取Excel文件并将其存储为DataFrame对象
    df = pd.read_excel(file_path, converters={'company_code': str})
    # 打印读取到的数据
    # print(df)
    print(type(df))
    feature_list = []
    executor = ThreadPoolExecutor(max_workers=12)
    for index, row in df.iterrows():
        feature = executor.submit(handle_row_data, (row))
        feature_list.append(feature)
    for f in feature_list:
        data_list = f.result()
        # data_list = handle_row_data(row)
        for data in data_list:
            province_list.append(data['province'])
            company_code_list.append(data['company_code'])
            cv_code_list.append(data['cv_code'])
            cv_type_list.append(data['cv_type'])
            sale_qty_list.append(data['sale_qty'])
            farm_list.append(data['farm'])
            farm_type_list.append(data['farm_type'])
            capacity_list.append(data['capacity'])
            capacity_crm_list.append(data['capacity_crm'])
            capacity_piglet_list.append(data['capacity_piglet'])
            capacity_piglet_crm_list.append(data['capacity_piglet_crm'])

    # 导出excel
    excel_data = {}
    excel_data['省'] = province_list
    excel_data['公司'] = company_code_list
    excel_data['cv_code'] = cv_code_list
    excel_data['客户类型'] = cv_type_list
    excel_data['10月销量'] = sale_qty_list
    excel_data['种猪规模crm'] = capacity_crm_list
    excel_data['肥猪规模crm'] = capacity_piglet_crm_list
    excel_data['农场代码'] = farm_list
    excel_data['种猪规模'] = capacity_list
    excel_data['肥猪规模'] = capacity_piglet_list
    excel_data['上线版本'] = farm_type_list

    df = pd.DataFrame(excel_data)
    path = "C:/Users/yangjianzhang/Desktop/"
    df.to_excel(path + '数据_{}.xlsx'.format(datetime.datetime.now().strftime('%Y%m%d%H%M%S')), index=False)
